﻿using System;
using System.Data;
using Agile.DataAccess.Providers.Common;
using Agile.DataAccess.Providers.Common.Builders;
using System.Text;

namespace Agile.DataAccess
{
	internal class OracleProvider : IDbProvider
	{
		public string ProviderName
		{ 
			get
			{
                return "Oracle.ManagedDataAccess.Client";
			} 
		}

		public bool SupportsOutputParameters
		{
			get { return true; }
		}

		public bool SupportsMultipleResultsets
		{
			get { return false; }
		}

		public bool SupportsMultipleQueries
		{
			get { return true; }
		}

		public bool SupportsStoredProcedures
		{
			get { return true; }
		}

		public bool RequiresIdentityColumn
		{
			get { return true; }
		}

		public IDbConnection CreateConnection(string connectionString)
		{
			return ConnectionFactory.CreateConnection(ProviderName, connectionString);
		}

		public string GetParameterName(string parameterName)
		{
			return ":" + parameterName;
		}

		public string GetSelectBuilderAlias(string name, string alias)
		{
			return name + " " + alias;
		}

		public string GetSqlForQueryBuilder(QueryBuilderData data)
		{
			var sql = "";
			if (data.PagingItemsPerPage == 0)
			{
				sql = "SELECT " + data.Select;
				sql += " FROM " + data.From;
				if (data.WhereSql.Length > 0)
					sql += " WHERE " + data.WhereSql;
				if (data.GroupBy.Length > 0)
					sql += " FROUP BY " + data.GroupBy;
				if (data.Having.Length > 0)
					sql += " HAVING " + data.Having;
                if (data.Orderby.Length > 0)
                    sql += " ORDER BY " + data.Orderby;
			}
			else if (data.PagingItemsPerPage > 0)
			{
				sql += " FROM " + data.From;
				if (data.WhereSql.Length > 0)
					sql += " WHERE " + data.WhereSql;
				if (data.GroupBy.Length > 0)
					sql += " GROUP BY " + data.GroupBy;
				if (data.Having.Length > 0)
					sql += " HAVING " + data.Having;

				sql = string.Format(@"SELECT * FROM
										(
											SELECT {0}, 
												row_number() over (ORDER BY {1}) FLUENTDATA_ROWNUMBER
											{2}
										)
										WHERE fluentdata_RowNumber between {3} AND {4}
										ORDER BY fluentdata_RowNumber",
											data.Select,
                                            data.Orderby,
											sql,
											data.GetFromItems(),
											data.GetToItems());
			}

			return sql;
		}

        public string GetSqlForSelectBuilder(SelectBuilderData data, bool isBuilder)
        {
            var sql = new StringBuilder();
            if (data.PagingItemsPerPage == 0)
            {
                sql.Append("SELECT ");
                var temp = string.Empty;
                foreach (var column in data.Columns)
                {
                    if (temp.Length > 0)
                        temp += ",";
                    temp += this.EscapeColumnName(column.ColumnName);
                }
                sql.Append(isBuilder ? temp : "* ");
                sql.Append("\r\nFROM ");
                sql.Append(data.ObjectName);

                temp = string.Empty;
                foreach (var column in data.Where)
                {
                    if (temp.Length > 0)
                        temp += " AND ";
                    temp += string.Format("{0} = {1}{2}", this.EscapeColumnName(column.ColumnName), ":", column.ParameterName);
                }
                if (temp.Length > 0)
                    sql.Append("\r\nWHERE " + temp);

                temp = string.Empty;
                foreach (var column in data.OrderBy)
                {
                    if (temp.Length > 0)
                        temp += ",";
                    temp += string.Format("{0} {1}", this.EscapeColumnName(column.ColumnName), column.Value);
                }
                if (temp.Length > 0)
                    sql.Append("\r\nORDER BY " + temp);
                return sql.ToString();
            }
            else
            {
                string oSql = string.Empty;
                var select = string.Empty;
                foreach (var column in data.Columns)
                {
                    if (select.Length > 0)
                        select += ",";
                    select += this.EscapeColumnName(column.ColumnName);
                }
                select = isBuilder ? select : "* ";
                string from = "\r\nFROM " + data.ObjectName;

                string temp = string.Empty;
                foreach (var column in data.Where)
                {
                    if (temp.Length > 0)
                        temp += " AND ";
                    temp += string.Format("{0} = {1}{2}", this.EscapeColumnName(column.ColumnName), ":", column.ParameterName);
                }
                if (temp.Length > 0)
                    from += "\r\nWHERE " + temp;

                string orderBy = string.Empty;
                foreach (var column in data.OrderBy)
                {
                    if (orderBy.Length > 0)
                        orderBy += ",";
                    orderBy += string.Format("{0} {1}", this.EscapeColumnName(column.ColumnName), column.Value);
                }

                oSql = string.Format(@"SELECT * FROM
(
SELECT {0}, row_number() over (ORDER BY {1}) FLUENTDATA_ROWNUMBER {2}
)
WHERE fluentdata_RowNumber between {3} AND {4}
ORDER BY fluentdata_RowNumber",
                                                       select,
                                                       orderBy,
                                                       from,
                                                       data.GetFromItems(),
                                                       data.GetToItems());

                return oSql.ToString();
            }
        }

		public string GetSqlForInsertBuilder(BuilderData data)
		{
			return new InsertBuilderSqlGenerator().GenerateSql(this, ":", data);
		}

		public string GetSqlForUpdateBuilder(BuilderData data)
		{
			return new UpdateBuilderSqlGenerator().GenerateSql(this, ":", data);
		}

		public string GetSqlForDeleteBuilder(BuilderData data)
		{
			return new DeleteBuilderSqlGenerator().GenerateSql(this, ":", data);
		}

        public string GetSqlForExistsBuilder(BuilderData data)
        {
            return new ExistsBuilderSqlGenerator().GenerateSql(this, ":", data, "SELECT COUNT(*) FROM {0} WHERE {1}");
        }

		public string GetSqlForStoredProcedureBuilder(BuilderData data)
		{
			return data.ObjectName;
		}

        public string GetSqlForPagingBuilder(SelectBuilderData data, string orderBy)
        {
            var sql = new StringBuilder();
            sql.Append(string.Format(@"SELECT * FROM
(
SELECT *, row_number() over (ORDER BY {1}) FLUENTDATA_ROWNUMBER FROM
    (
{0}
    } AS t                  
)
WHERE fluentdata_RowNumber between {1} AND {2}
ORDER BY fluentdata_RowNumber",
                                        data.ObjectName,
                                        data.GetFromItems(),
                                        data.GetToItems()));
            return sql.ToString();
        }

		public DataTypes GetDbTypeForClrType(Type clrType)
		{
			return new DbTypeMapper().GetDbTypeForClrType(clrType);
		}

		public object ExecuteReturnLastId<T>(IDbCommand command, string identityColumnName = null)
		{
			command.ParameterOut("FluentDataLastId", command.Data.Context.Data.FluentDataProvider.GetDbTypeForClrType(typeof(T)));
			command.Script(" returning " + identityColumnName + " into :FluentDataLastId");


			object lastId = null;

			command.Data.ExecuteQueryHandler.ExecuteQuery(false, () =>
			{
				command.Data.InnerCommand.ExecuteNonQuery();

				lastId = command.ParameterValue<object>("FluentDataLastId");
			});

			return lastId;
		}

		public void OnCommandExecuting(IDbCommand command)
		{
			if (command.Data.InnerCommand.CommandType == CommandType.Text)
			{
				dynamic innerCommand = command.Data.InnerCommand;
				innerCommand.BindByName = true;
			}
		}

		public string EscapeColumnName(string name)
		{
			return name;
		}
	}
}
